{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "19413aad",
   "metadata": {},
   "source": [
    "#### 写出导入pandas的语句，并且给pandas别名为`pd`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "74b1dbfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fe373ba",
   "metadata": {},
   "source": [
    "#### 创建一个名为`students`的DataFrame，包含数据如下：\n",
    "```{\"001\": {\"姓名\": \"小陈\", \"考试1\": 85, \"考试2\": 95, \"考试3\": 92},\n",
    " \"002\": {\"姓名\": \"小李\", \"考试1\": 91, \"考试2\": 92, \"考试3\": 94},\n",
    " \"003\": {\"姓名\": \"小王\", \"考试1\": 86, \"考试2\": 81, \"考试3\": 89},\n",
    " \"004\": {\"姓名\": \"小张\", \"考试1\": 79, \"考试2\": 89, \"考试3\": 95},\n",
    " \"005\": {\"姓名\": \"小赵\", \"考试1\": 96, \"考试2\": 91, \"考试3\": 91},\n",
    " \"006\": {\"姓名\": \"小周\", \"考试1\": 81, \"考试2\": 89, \"考试3\": 92}\n",
    " }```\n",
    " \n",
    " #### 要求学号（001、002等）为索引，姓名、考试1、考试2、考试3为列名，并输出创建好的DataFrame。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7f8b7952",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>考试1</th>\n",
       "      <th>考试2</th>\n",
       "      <th>考试3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>001</th>\n",
       "      <td>小陈</td>\n",
       "      <td>85</td>\n",
       "      <td>95</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002</th>\n",
       "      <td>小李</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>003</th>\n",
       "      <td>小王</td>\n",
       "      <td>86</td>\n",
       "      <td>81</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>004</th>\n",
       "      <td>小张</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>005</th>\n",
       "      <td>小赵</td>\n",
       "      <td>96</td>\n",
       "      <td>91</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>006</th>\n",
       "      <td>小周</td>\n",
       "      <td>81</td>\n",
       "      <td>89</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     姓名 考试1 考试2 考试3\n",
       "001  小陈  85  95  92\n",
       "002  小李  91  92  94\n",
       "003  小王  86  81  89\n",
       "004  小张  79  89  95\n",
       "005  小赵  96  91  91\n",
       "006  小周  81  89  92"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student_data = {\"001\": {\"姓名\": \"小陈\", \"考试1\": 85, \"考试2\": 95, \"考试3\": 92},\n",
    " \"002\": {\"姓名\": \"小李\", \"考试1\": 91, \"考试2\": 92, \"考试3\": 94},\n",
    " \"003\": {\"姓名\": \"小王\", \"考试1\": 86, \"考试2\": 81, \"考试3\": 89},\n",
    " \"004\": {\"姓名\": \"小张\", \"考试1\": 79, \"考试2\": 89, \"考试3\": 95},\n",
    " \"005\": {\"姓名\": \"小赵\", \"考试1\": 96, \"考试2\": 91, \"考试3\": 91},\n",
    " \"006\": {\"姓名\": \"小周\", \"考试1\": 81, \"考试2\": 89, \"考试3\": 92}}\n",
    "students = pd.DataFrame(student_data).T\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8e6c121",
   "metadata": {},
   "source": [
    "#### 创建一个叫`student_average`的DataFrame，索引为学号，包含姓名列（列名为“姓名”），以及计算出四次考试平均值后的结果列（列名为“平均分”）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "625913c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>平均分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>001</th>\n",
       "      <td>小陈</td>\n",
       "      <td>90.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002</th>\n",
       "      <td>小李</td>\n",
       "      <td>92.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>003</th>\n",
       "      <td>小王</td>\n",
       "      <td>85.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>004</th>\n",
       "      <td>小张</td>\n",
       "      <td>87.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>005</th>\n",
       "      <td>小赵</td>\n",
       "      <td>92.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>006</th>\n",
       "      <td>小周</td>\n",
       "      <td>87.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     姓名        平均分\n",
       "001  小陈  90.666667\n",
       "002  小李  92.333333\n",
       "003  小王  85.333333\n",
       "004  小张  87.666667\n",
       "005  小赵  92.666667\n",
       "006  小周  87.333333"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average = students.loc[:, \"考试1\":\"考试3\"].mean(axis=1)\n",
    "name = students[\"姓名\"]\n",
    "student_average = pd.DataFrame({\"姓名\":name, \"平均分\":average})\n",
    "student_average"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05a3003f",
   "metadata": {},
   "source": [
    "#### 输出每次考试第二高的分数组成的Series，可以尝试结合匿名函数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d08ff9f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "考试1    91\n",
       "考试2    92\n",
       "考试3    94\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.loc[:, \"考试1\":\"考试3\"].apply(lambda x:np.sort(x)[-2])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2d4a3b0",
   "metadata": {},
   "source": [
    "#### 根据以下规则，输出每个学号对应的四次考试的等级：\n",
    "- A+：平均分 >= 95\n",
    "- A：90 <= 平均分 < 95\n",
    "- B+：85 <= 平均分 < 90\n",
    "- B：80 <= 平均分 < 85\n",
    "- C+：75 <= 平均分 < 80\n",
    "- C：平均分 < 75"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "80d3983e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_grade(score):\n",
    "    if score >= 95:\n",
    "        return \"A+\"\n",
    "    elif score >= 90:\n",
    "        return \"A\"\n",
    "    elif score >= 85:\n",
    "        return \"B\"\n",
    "    elif score >= 80:\n",
    "        return \"B+\"\n",
    "    elif score >= 75:\n",
    "        return \"C+\"\n",
    "    else:\n",
    "        return \"C\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8a322a76-b91b-4af0-9068-ea0d75947407",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>考试1</th>\n",
       "      <th>考试2</th>\n",
       "      <th>考试3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>001</th>\n",
       "      <td>B</td>\n",
       "      <td>A+</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>003</th>\n",
       "      <td>B</td>\n",
       "      <td>B+</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>004</th>\n",
       "      <td>C+</td>\n",
       "      <td>B</td>\n",
       "      <td>A+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>005</th>\n",
       "      <td>A+</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>006</th>\n",
       "      <td>B+</td>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    考试1 考试2 考试3\n",
       "001   B  A+   A\n",
       "002   A   A   A\n",
       "003   B  B+   B\n",
       "004  C+   B  A+\n",
       "005  A+   A   A\n",
       "006  B+   B   A"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.loc[:, \"考试1\":\"考试3\"].applymap(get_grade)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bf7cfa4",
   "metadata": {},
   "source": [
    "#### 展示`students`每个考试分数数据列的统计信息，包括个数、平均值、标准差、四分位数等。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "460318f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>考试1</th>\n",
       "      <th>考试2</th>\n",
       "      <th>考试3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>86.333333</td>\n",
       "      <td>89.500000</td>\n",
       "      <td>92.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.314006</td>\n",
       "      <td>4.722288</td>\n",
       "      <td>2.136976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>79.000000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>89.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>82.000000</td>\n",
       "      <td>89.000000</td>\n",
       "      <td>91.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>85.500000</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>92.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>89.750000</td>\n",
       "      <td>91.750000</td>\n",
       "      <td>93.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>96.000000</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>95.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             考试1        考试2        考试3\n",
       "count   6.000000   6.000000   6.000000\n",
       "mean   86.333333  89.500000  92.166667\n",
       "std     6.314006   4.722288   2.136976\n",
       "min    79.000000  81.000000  89.000000\n",
       "25%    82.000000  89.000000  91.250000\n",
       "50%    85.500000  90.000000  92.000000\n",
       "75%    89.750000  91.750000  93.500000\n",
       "max    96.000000  95.000000  95.000000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students[\"考试1\"] = students[\"考试1\"].astype('int')\n",
    "students[\"考试2\"] = students[\"考试2\"].astype('int')\n",
    "students[\"考试3\"] = students[\"考试3\"].astype('int')\n",
    "students.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e554fe9-ac0a-447b-bb04-13b2d594efea",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3080ce19-64cc-496c-945f-874c7af08884",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
